﻿/*
 set dateformat dmy
 go
 Sp_rptCFT07_154 '01/01/2012', '31/12/2012','','511', '154', 
	'621','622','623','627','641','642',
	'154,621,622,623,627,641,642', 
	'111,112,113,141,142,152,153,155,156,157,159' 

*/
-- T?ng h?p chi phí theo s?n ph?m công trình
alter PROCEDURE [dbo].[Sp_rptCFT07_154]
(
	@Ngay_Ct1 DATETIME = '',
	@Ngay_Ct2 DATETIME = '',	
	@Ma_Sp VARCHAR(20) = '',
	@Tk_511 VARCHAR(10) = '511',	
	@Tk_154 varchar(10) = '154',
	@Tk_621 VARCHAR(10) = '621',
	@Tk_622 VARCHAR(10) = '622',
	@Tk_623 VARCHAR(10) = '623',
	@Tk_627 VARCHAR(10) = '627',
	@Tk_641 VARCHAR(10) = '641',
	@Tk_642 VARCHAR(10) = '642',
	@Tk_Cf_List VARCHAR(200) = '',
	@Tk_Giam_Cf_List VARCHAR(200) = '',	
	@Is_Vnd BIT = 1,
	@Language_Type CHAR(1) = 'V',
	@Ma_DvCs VARCHAR(5) = 'A01'
)
--WITH ENCRYPTION
AS
BEGIN
	
	DECLARE @_KeyDt VARCHAR(1000),
			@_KeyCf VARCHAR(1000),
			@_Tk_Cf_List VARCHAR(1000),
			@_SqlExec VARCHAR(1000)
			
	SELECT @_KeyCf = '( 0 = 0)', @_KeyDt = '( 0 = 0)'
	
	
	IF @Ma_Sp <> ''
		BEGIN	
			SET @_KeyCf = @_KeyCf + ' AND (Ma_Sp = ''' + @Ma_Sp + ''')'
--			SET @_KeyDt = @_KeyDt + ' AND (Ma_Sp = ''' + @Ma_Sp + ''')'	
			SET @_KeyDt = @_KeyDt + ' AND (Ma_Vt = ''' + @Ma_Sp + ''')'	

		END	
	
	IF(@Tk_511 <> '')
		SET @_KeyDt = @_KeyDt + ' AND (Tk LIKE ''' + @Tk_511 + '%'')'	
		
		SET @_KeyDt = @_KeyDt + ' AND (Loai_Ct = ''1'') AND (Ma_Vt <> '''')' 
		
	
	IF(@Tk_Cf_List <> '' AND @Tk_Giam_Cf_List <> '')
		BEGIN
			SET @_KeyCf = @_KeyCf + ' AND( Tk LIKE ''' + REPLACE(@Tk_Cf_List, ',', '%'' OR Tk LIKE ''') + '%'')'
			SET @_KeyCf = @_KeyCf + ' AND (Loai_Ct = ''1'' OR (Loai_Ct = ''2''  AND Tk_Du LIKE ''' + REPLACE(@Tk_Giam_Cf_List, ',', '%'' OR Tk_Du LIKE ''') + '%''))'		
		END	
	ELSE IF (@Tk_Cf_List <> '')
		BEGIN
			SET @_KeyCf = @_KeyCf + ' AND(Loai_Ct = ''1'' AND (Tk LIKE ''' + REPLACE(@Tk_Cf_List, ',', '%'' OR Tk LIKE ''') + '%''))'
		END
	ELSE IF (@Tk_Giam_Cf_List <> '')
		BEGIN
			SET @_KeyCf = @_KeyCf + ' AND (Loai_Ct = ''1'' OR (Loai_Ct = ''2''  AND Tk_Du LIKE ''' + REPLACE(@Tk_Giam_Cf_List, ',', '%'' OR Tk_Du LIKE ''') + '%''))'		
		END			
		
	SET @_KeyCf = @_KeyCf + ' AND (Ma_Sp <> '''')'		
	
	--#T_PhatSinhDt
	SELECT	Tk, Ma_Sp, Ma_Vt, Ps_Tang, Ps_Giam, CAST(0 AS MONEY) AS Tien
		INTO #T_PhatSinhDt
		FROM R80DoanhThu
		WHERE 0 = 1
		
	--#T_PhatSinhCf	
	SELECT	Tk, Ma_Sp,Ps_Tang, Ps_Giam, CAST(0 AS MONEY) AS Tien
		INTO #T_PhatSinhCf
		FROM R80ChiPhi
		WHERE 0 = 1	


	
	EXEC Sp_DefaultTable '#T_PhatSinhDt'	
	EXEC Sp_ScanTableDoanhThu @Ngay_Ct1, @Ngay_Ct2, @_KeyDt, '#T_PhatSinhDt', @Ma_DvCs			

	update #T_PhatSinhDt set Ma_Sp = Ma_Vt 

--YTH
/*
set dateformat dmy 
go
Sp_rptCFT07 '01/02/2010', '28/02/2010','1ASAIGON','511','621','622','623','627','641','642','621,622,623,627,641,642', '111,112,113,141,142,152,153,155,156,157,159' 

select * from r80doanhthu
select * from #T_PhatSinhDt	

*/
--
--select @_KeyDt
	SELECT Tk, Ma_Sp, Du_No, Du_Co, Du_No_Nt, Du_Co_Nt
		INTO #T_SoDuDau
		FROM R80CDK
		WHERE 0 = 1
	
	declare @_KeyCD varchar(1000)	
	set @_KeyCD = '(Tk LIKE ''' + @Tk_154 + '%'')'
	
	EXEC Sp_SoDuDauTkList @Ngay_Ct1, @_KeyCD, '#T_SoDuDau', @Ma_DvCs
	
	--select * from #T_SoDuDau
	--return
	
	EXEC Sp_DefaultTable '#T_PhatSinhCf'	
	EXEC Sp_ScanTableChiPhi @Ngay_Ct1, @Ngay_Ct2, @_KeyCf, '#T_PhatSinhCf', @Ma_DvCs			

	UPDATE #T_PhatSinhDt SET Tien = Ps_Tang - Ps_Giam
	UPDATE #T_PhatSinhCf SET Tien = Ps_Tang - Ps_Giam	
	
	insert #T_PhatSinhCf(Tk, Ma_Sp, Tien)
		select Tk, case when Ma_Sp = '' then 'ZZZ' else Ma_Sp end, 
				sum(Du_No - Du_Co)
			from #T_SoDuDau 
			group by Tk, Ma_Sp
	--#T_PhatSinh
	SELECT Tk, Ma_Sp, Ps_Tang, Ps_Giam, Tien
		INTO #T_PhatSinh
		FROM #T_PhatSinhDt
		
	INSERT #T_PhatSinh
		SELECT * 
			FROM #T_PhatSinhCf	

	CREATE TABLE #T_BaoCao(	
		 Ma_Sp VARCHAR(20), Ten_Sp NVARCHAR(100),
		 Tong_Cong MONEY, Tk_154 MONEY, Tk_621 MONEY, Tk_622 MONEY,
		 Tk_623 MONEY, Tk_627 MONEY, Tk_641 MONEY, 
		 Tk_642 MONEY, Tk_Khac MONEY, Tk_511 MONEY, Tk_Dt_Cf MONEY,
		 Status CHAR(1), Bold BIT)	
	

	UPDATE #T_PhatSinh SET Tk = @Tk_511	WHERE Tk LIKE + @Tk_511 + '%'	
	UPDATE #T_PhatSinh SET Tk = @Tk_154	WHERE Tk LIKE + @Tk_154 + '%'	
	UPDATE #T_PhatSinh SET Tk = @Tk_621	WHERE Tk LIKE + @Tk_621 + '%'	
	UPDATE #T_PhatSinh SET Tk = @Tk_622	WHERE Tk LIKE + @Tk_622 + '%'
	UPDATE #T_PhatSinh SET Tk = @Tk_623	WHERE Tk LIKE + @Tk_623 + '%'		
	UPDATE #T_PhatSinh SET Tk = @Tk_627	WHERE Tk LIKE + @Tk_627 + '%'	
	UPDATE #T_PhatSinh SET Tk = @Tk_641	WHERE Tk LIKE + @Tk_641 + '%'	
	UPDATE #T_PhatSinh SET Tk = @Tk_642	WHERE Tk LIKE + @Tk_642 + '%'

	SET @_SqlExec = '	
		UPDATE #T_PhatSinh SET 
			Tk = ''Tk_Khac'' 
			WHERE Tk NOT LIKE ''' + @Tk_621 + '%'' AND Tk NOT LIKE ''' + @Tk_622 + '%'' AND Tk NOT LIKE ''' + @Tk_623 +
					+ '%'' AND Tk NOT LIKE ''' + @Tk_154 + '%'' AND Tk NOT LIKE ''' + @Tk_627 + '%'' AND Tk NOT LIKE ''' + @Tk_641 + '%'' AND Tk NOT LIKE ''' 
					+ @Tk_642 + '%'' AND Tk NOT LIKE ''' + @Tk_511 + '%'''				 

	EXECUTE (@_SqlExec)		
	
	SET @_SqlExec = '
		SELECT Ma_Sp, ISNULL([511], 0) AS Tk_511, ISNULL([154], 0) AS Tk_154, ISNULL([621], 0) AS Tk_621, ISNULL([622], 0) AS Tk_622, ISNULL([623], 0) AS Tk_623,
				ISNULL([627], 0) AS Tk_627, ISNULL([641], 0) AS Tk_641, ISNULL([642], 0) AS Tk_642, ISNULL([Tk_Khac], 0) AS Tk_Khac			
			FROM
				(SELECT Ma_Sp, Tk, Tien FROM #T_PhatSinh ) AS p
			PIVOT
				(
					SUM(Tien) FOR Tk IN ([' + @Tk_511 + '],[' + @Tk_154 + '],[' + @Tk_621 + '],[' + @Tk_622 + '],[' + @Tk_623 + '],[' + @Tk_627 + '],[' + @Tk_641 + '],[' + @Tk_642 + '],[Tk_Khac])	
				) AS unp'
				
	INSERT #T_BaoCao(Ma_Sp, Tk_511, Tk_154, Tk_621, Tk_622, Tk_623, Tk_627, Tk_641, Tk_642, Tk_Khac)	
		EXECUTE (@_SqlExec)	

--select 	* from #T_BaoCao

	UPDATE #T_BaoCao SET
		Tong_Cong = Tk_154 + Tk_621 + Tk_622 + Tk_623 + Tk_627 + Tk_641 + Tk_642 + Tk_Khac,		
		Status = '1', 
		Bold = 0	

	UPDATE #T_BaoCao SET	
		Tk_Dt_Cf = Tk_511 - Tong_Cong		
		
	UPDATE #T_BaoCao	SET 
			Ten_Sp = R81DMSP.Ten_Sp	
		FROM #T_BaoCao JOIN R81DMSP ON #T_BaoCao.Ma_Sp = R81DMSP.Ma_Sp
	
	update #T_BaoCao set Ten_Sp = N'Chưa xác định sản phẩm' 
		where Ma_Sp = 'ZZZ'
	
	EXECUTE Sp_DefaultTable '#T_BaoCao'
	
	INSERT #T_BaoCao (Ten_Sp, Tk_511, Tong_Cong, Tk_Dt_Cf, Tk_154, Tk_621, Tk_622, Tk_623, Tk_627, Tk_641, Tk_642, Tk_Khac, Status, Bold)
		SELECT	dbo.fn_GetLanguage('Tong_Cong', @Language_Type), 
				ISNULL(SUM(Tk_511), 0) AS Tk_511, ISNULL(SUM(Tong_Cong), 0) AS Tong_Cong, 
				ISNULL(SUM(Tk_Dt_Cf), 0) AS Tk_Dt_Cf, 
				ISNULL(SUM(Tk_154), 0) AS Tk_154, 
				ISNULL(SUM(Tk_621), 0) AS Tk_621, 
				ISNULL(SUM(Tk_622), 0) AS Tk_622, ISNULL(SUM(Tk_623),0) AS Tk_623,
				ISNULL(SUM(Tk_627), 0) AS Tk_627, ISNULL(SUM(Tk_641),0) AS Tk_641,
				ISNULL(SUM(Tk_642), 0) AS Tk_642, ISNULL(SUM(Tk_Khac),0) AS Tk_Khac, 
				'2', 1
			FROM #T_BaoCao		
	
	SELECT * 
		FROM #T_BaoCao 
		ORDER By Status, Ma_Sp

END








